<?php
/**
 * 新旧数据库改动对比辅助工具
 *
 * */

/**
 * 数据库配置信息
 * */
class DbConfig
{
    public $server;         // 服务器地址
    public $port;           // 服务器端口
    public $user;           // 用户
    public $pwd;            // 密码
    public $database;       // 数据库

    /**
     * @param string    $server     // 服务器地址
     * @param string    $port       // 服务器端口
     * @param string    $user       // 用户
     * @param string    $pwd        // 密码
     * @param string    $database   // 数据库
     * */
    public function __construct(string $server, string $port, string $user, string $pwd, string $database)
    {
        $this->server = $server;
        $this->port = $port;
        $this->user = $user;
        $this->pwd = $pwd;
        $this->database = $database;
    }
}


/**
 * 数据库对比工具类, 以新数据库做参照, 生成旧数据库如何转换为新数据库的SQL
 * */
class Differ
{
    protected $dbOld;                       // 旧数据库配置信息
    protected $dbNew;                       // 新数据库配置信息

    protected $connNew;                     // 新数据库连接
    protected $connOld;                     // 旧数据库连接

    protected $createTable;                 // 新增的表信息
    protected $deleteTable;                 // 删除的表信息
    protected $diffTable;                   // 需要对比的表

    protected $columnChange = [];           // 修改的字段信息
    protected $columnDelete = [];           // 删除的字段信息
    protected $columnCreate = [];           // 新增的字段信息

    protected $defaultEngine = 'InnoDB';    // 默认存储引擎
    protected $defaultCharset = 'utf8';     // 默认编码
    protected $defaultComment = '';         // 默认备注

    protected $tableCreate = [];            // 表创建 sql 语句列表
    protected $tableDelete = [];            // 表删除 sql 语句列表
    protected $tableChange = [];            // 表更改 sql 语句列表

    /**
     * 初始化
     * @param DbConfig  $dbNew              新的数据库配置信息
     * @param DbConfig  $dbOld              旧的数据库配置信息
     * @param string    $defaultEngine      默认数据表引擎
     * @param string    $defaultCharset     默认字符集
     * @param string    $defaultComment     默认备注信息
     * */
    public function __construct(DbConfig $dbNew, DbConfig $dbOld, string $defaultEngine = 'InnoDB', string $defaultCharset = 'utf8', string $defaultComment = '') {
        $this->dbNew = $dbNew;
        $this->dbOld = $dbOld;
        $this->defaultEngine = $defaultEngine;
        $this->defaultCharset = $defaultCharset;
        $this->defaultComment = $defaultComment;
        $this->connect();
    }

    /**
     * 处理新旧数据库对比
     * @return Differ
     * */
    public function process() {
        $this->allTables();
        $this->diffTables();
        return $this;
    }

    /**
     * 导出迁移 sql 文件
     * @param string    $saveName 保存文件名
     * @return Differ
     * */
    public function exportSql(string $saveName = '') {
        if ($saveName === '') $saveName = date('Ymd', time()).'.sql';
        $f = fopen($saveName, 'w+');

        // 删除表
        fwrite($f, sprintf("-- DROP TABLES -------------------------------------------------------\r\n"));
        foreach ($this->tableDelete as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 新增表
        fwrite($f, sprintf("\r\n-- CREATE TABLE ------------------------------------------------------\r\n"));
        foreach ($this->tableCreate as $item){
            fwrite($f, sprintf("%s\r\n\r\n", $item));
        }

        // 修改表
        fwrite($f, sprintf("\r\n-- MODIFY TABLE ------------------------------------------------------\r\n"));
        foreach ($this->tableChange as $item){
            fwrite($f, sprintf("%s\r\n\r\n", $item));
        }

        // 删除列信息
        fwrite($f, sprintf("\r\n-- DROP COLUMN -------------------------------------------------------\r\n"));
        foreach ($this->columnDelete as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 新增列信息
        fwrite($f, sprintf("\r\n-- CREATE COLUMN -----------------------------------------------------\r\n"));
        foreach ($this->columnCreate as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 修改列信息
        fwrite($f, sprintf("\r\n-- MODIFY COLUMN -----------------------------------------------------\r\n"));
        foreach ($this->columnChange as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        fclose($f);
        return $this;
    }

    /**
     * 打印迁移 sql 文件
     * @return Differ
     * */
    public function printSql() {
        $f = STDOUT;

        // 删除表
        fwrite($f, sprintf("-- DROP TABLES -------------------------------------------------------\r\n"));
        foreach ($this->tableDelete as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 新增表
        fwrite($f, sprintf("\r\n-- CREATE TABLE ------------------------------------------------------\r\n"));
        foreach ($this->tableCreate as $item){
            fwrite($f, sprintf("%s\r\n\r\n", $item));
        }

        // 修改表
        fwrite($f, sprintf("\r\n-- MODIFY TABLE ------------------------------------------------------\r\n"));
        foreach ($this->tableChange as $item){
            fwrite($f, sprintf("%s\r\n\r\n", $item));
        }

        // 删除列信息
        fwrite($f, sprintf("\r\n-- DROP COLUMN -------------------------------------------------------\r\n"));
        foreach ($this->columnDelete as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 新增列信息
        fwrite($f, sprintf("\r\n-- CREATE COLUMN -----------------------------------------------------\r\n"));
        foreach ($this->columnCreate as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        // 修改列信息
        fwrite($f, sprintf("\r\n-- MODIFY COLUMN -----------------------------------------------------\r\n"));
        foreach ($this->columnChange as $item){
            fwrite($f, sprintf("%s\r\n", $item));
        }

        return $this;
    }

    /**
     * 清理连接
     * */
    public function __destruct() {
        mysqli_close($this->connNew);
        mysqli_close($this->connOld);
    }

    /**
     * 对比表字段信息
     * */
    protected function diffTables() {

        // 新旧表对比处理
        foreach ($this->diffTable as $idx => $item){
            // 新的表结构信息
            $resNew = mysqli_query($this->connNew, sprintf('SHOW CREATE TABLE `%s`;', $item));
            $newTableInfo = $this->tableInfo(mysqli_fetch_all($resNew));

            // 旧的表结构信息
            $resOld = mysqli_query($this->connOld, sprintf('SHOW CREATE TABLE `%s`;', $item));
            $oldTableInfo = $this->tableInfo(mysqli_fetch_all($resOld));

            // 生成字段对比信息
            $this->diffColumn($item, $newTableInfo['column'], $oldTableInfo['column']);

            // 生成表属性对比信息
            $this->diffTableAttr($item, $newTableInfo['attr'], $oldTableInfo['attr']);
        }

        // 新增表处理
        foreach ($this->createTable as $idx => $item){
            $this->createTable($item);
        }

        // 删除表处理
        foreach ($this->deleteTable as $idx => $item){
            $this->dropTable($item);
        }
    }

    /**
     * 生成表删除 sql
     * @param string    $tableName
     * */
    protected function dropTable(string $tableName) {
        array_push($this->tableDelete, sprintf('DROP TABLE IF EXISTS `%s`;', $tableName));
    }

    /**
     * 生成表创建 sql
     * @param string    $tableName
     * */
    protected function createTable(string $tableName) {
        $newRes = mysqli_query($this->connNew, sprintf("SHOW CREATE TABLE `%s`;", $tableName));
        $newTableCreateSql = mysqli_fetch_all($newRes);
        $newTableCreateSql = $newTableCreateSql[0][1];
        $newTableCreateSql = preg_replace("/\sAUTO_INCREMENT=[0-9]+/", "", $newTableCreateSql);
        $newTableCreateSql = preg_replace("/\sROW_FORMAT=[A-z]+/", "", $newTableCreateSql);
        $newTableCreateSql = preg_replace("/\sUSING\s[A-z]+/", "", $newTableCreateSql);
        array_push($this->tableCreate, $newTableCreateSql.";");
    }

    /**
     * 生成表属性更改 sql
     * @param string    $tableName
     * @param array     $newTableAttr
     * @param array     $oldTableAttr
     * */
    protected function diffTableAttr(string $tableName, array $newTableAttr, array $oldTableAttr) {
        if ($newTableAttr['engine'] !== $oldTableAttr['engine']
            || $newTableAttr['charset'] !== $oldTableAttr['charset']
            || $newTableAttr['comment'] !== $oldTableAttr['comment']) {
            $sql = sprintf("ALTER TABLE `%s` ENGINE=%s CHARACTER SET %s COMMENT='%s';", $tableName, $newTableAttr['engine'], $newTableAttr['charset'], $newTableAttr['charset']);
            array_push($this->tableChange, $sql);
        }
    }

    /**
     * 对比列属性并生成 sql
     * @param string    $tableName
     * @param array    $newColumnInfo
     * @param array    $oldColumnInfo
     * @return void
     * */
    protected function diffColumn(string $tableName, array $newColumnInfo, array $oldColumnInfo) {
        $delColumn = [];
        $addColumn = [];
        $diffColumn = [];
        foreach ($newColumnInfo as $idx => $item){
            if (!isset($oldColumnInfo[$idx])){
                array_push($addColumn, sprintf('ALTER TABLE `%s` ADD COLUMN `%s` %s;', $tableName, $item['column'], $item['attr']));
            }else{
                if ($oldColumnInfo[$idx]['attr'] !== $item['attr']){
                    array_push($diffColumn, sprintf('ALTER TABLE `%s` MODIFY COLUMN `%s` %s;', $tableName, $item['column'], $item['attr']));
                }
            }
        }

        foreach ($oldColumnInfo as $idx => $item){
            if (!isset($newColumnInfo[$idx])){
                if (is_array($item)){
                    array_push($delColumn, sprintf('ALTER TABLE `%s` DROP COLUMN `%s`;', $tableName, $item['column']));
                }else{
                    array_push($delColumn, sprintf('ALTER TABLE `%s` DROP COLUMN `%s`;', $tableName, $item));
                }
            }
        }

        $this->columnChange = array_merge($this->columnChange, $diffColumn);
        $this->columnCreate = array_merge($this->columnCreate, $addColumn);
        $this->columnDelete = array_merge($this->columnDelete, $delColumn);
    }

    /**
     * 获取表信息
     * @param array|null    $tableRawInfo
     * @return array
     * */
    protected function tableInfo(array $tableRawInfo) :array {
        $tableInfo = [];

        // 表名称
        preg_match('/^CREATE TABLE `([A-z]+)`/', $tableRawInfo[0][1], $tableName);
        $tableInfo['table'] = $tableName[1];

        // 表字段信息
        $reg = "/\s{2,3}([^\n]+),\s/m";
        preg_match_all($reg, $tableRawInfo[0][1], $res);
        $tableInfo['column'] = $this->columnInfo($res[1]);

        // 表属性信息
        $tableAttrReg = "/\n\)\s([^\n]+)$/";
        preg_match($tableAttrReg, $tableRawInfo[0][1], $tableAttr);
        $tableAttrStr = $tableAttr[1];

        // 剔除可能在执行 sql 时出现不兼容的属性
        $tableAttrStr = preg_replace("/\sAUTO_INCREMENT=[0-9]+/", "", $tableAttrStr);
        $tableAttrStr = preg_replace("/\sROW_FORMAT=[A-z]+/", "", $tableAttrStr);

        // 获取表属性
        $tableInfo['attr'] = $this->tableAttr($tableAttrStr);

        return $tableInfo;
    }

    /**
     * 获取表属性(表引擎, 备注信息, 字符集)
     * @param string    $tableStr
     * @return array
     * */
    protected function tableAttr(string $tableStr) :array {
        $res = [];
        preg_match("/ENGINE=([A-z]+)\s/", $tableStr, $engine);
        preg_match("/DEFAULT\sCHARSET=([A-z0-9_]+)\s/", $tableStr, $charSet);
        preg_match("/COMMENT='(.*)'/", $tableStr, $comment);
        $res['engine'] = isset($engine[1]) ? $engine[1] : $this->defaultEngine;
        $res['charset'] = isset($charSet[1]) ? $charSet[1] : $this->defaultCharset;
        $res['comment'] = isset($comment[1]) ? $comment[1] : $this->defaultComment;
        return $res;
    }

    /**
     * 获取列信息
     * @param array $columns
     * @return array
     * */
    protected function columnInfo(array $columns) :array {
        $res = [];
        $columnReg = "/^`([^`]+)`\s(.*)$/";
        foreach ($columns as $idx => $item){
            preg_match_all($columnReg, $item, $tmp);
            if (isset($tmp[1]) && $tmp[1] && isset($tmp[1][0]) && $tmp[1][0]){
                $res[$tmp[1][0]] = [
                    'column'    => $tmp[1][0],
                    'attr'      => $tmp[2][0],
                ];
            }
        }
        return $res;
    }

    /**
     * 获取所有的数据表, 并进行大致归类
     * */
    protected function allTables() {
        $resNew = mysqli_query($this->connNew, 'SHOW TABLES;');
        $resOld = mysqli_query($this->connOld, 'SHOW TABLES;');
        $tablesNew = mysqli_fetch_all($resNew);
        $tablesOld = mysqli_fetch_all($resOld);

        $tablesNewMap = [];
        $tablesOldMap = [];
        foreach ($tablesNew as $idx => $item){
            $tablesNewMap[$item[0]] = true;
        }
        foreach ($tablesOld as $idx => $item){
            $tablesOldMap[$item[0]] = true;
        }

        $delTables = [];
        $addTables = [];
        $diffTables = [];

        // 新增的表
        foreach ($tablesNewMap as $idx => $item){
            if (!isset($tablesOldMap[$idx])){   // 新增的表
                array_push($addTables, $idx);
            }else{                              // 原有的表, 加入需要对比的数组
                array_push($diffTables, $idx);
            }
        }
        // 删除的表
        foreach ($tablesOldMap as $idx => $item){
            if (!isset($tablesNewMap[$idx])){
                array_push($delTables, $idx);
            }
        }

        $this->deleteTable = $delTables;
        $this->createTable = $addTables;
        $this->diffTable   = $diffTables;
    }

    /**
     * 建立新旧数据库连接
     * */
    protected function connect() {
        $this->connNew = mysqli_connect(
            $this->dbNew->server,
            $this->dbNew->user,
            $this->dbNew->pwd,
            $this->dbNew->database,
            $this->dbNew->port
        ) OR die('New Database Connect Failed');

        $this->connOld = mysqli_connect(
            $this->dbOld->server,
            $this->dbOld->user,
            $this->dbOld->pwd,
            $this->dbOld->database,
            $this->dbOld->port
        ) OR die('Old Database Connect Failed');

        mysqli_select_db($this->connNew, $this->dbNew->database);
        mysqli_select_db($this->connOld, $this->dbOld->database);
        mysqli_query($this->connNew, 'SET NAMES utf8;');
        mysqli_query($this->connOld, 'SET NAMES utf8;');
    }
}

$old = new DbConfig('127.0.0.1', '3306', 'root', 'root', 'test_old');
$new = new DbConfig('127.0.0.1', '3306', 'root', 'root', 'test');

$differ = new Differ($new, $old);
$differ->process()->exportSql('sqlChange.sql');
